package com.gxuwz.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gxuwz.common.ICrud;
import com.gxuwz.entity.Role;
import com.gxuwz.entity.User;
import com.gxuwz.util.DBUtil;

public class UserDao implements ICrud<User> {

	/** 数据库连接 */
	private Connection conn = null;
	/** 使用预编译执行SQL */
	private PreparedStatement ptmt = null;
	/** 查询数据返回的结果集 */
	private ResultSet rs = null;

	@Override
	public boolean insert(User t) {
		conn = DBUtil.get();
		// ptmt.execute() 执行成功返回 false
		boolean flag = true;
		// 问号相当于占位符
		String sql = "insert into user"
				+ "(name,age,sex,username,password,role_id,create_time)"
				+ " values(?,?,?,?,?,?,?)";
		try {
			ptmt = conn.prepareStatement(sql);
			
			

			// 使用真实的数据替换sql语句中的问号
			ptmt.setString(1, t.getName());
			ptmt.setInt(2, t.getAge());
			ptmt.setInt(3, t.getSex());
			ptmt.setString(4, t.getUsername());
			ptmt.setString(5, t.getPassword());
			ptmt.setInt(6, t.getRoleId());
			ptmt.setString(7, t.getCreateTime());

			flag = ptmt.execute();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 使用完之后关闭数据库的连接，释放资源
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public boolean delete(String id) {
		conn = DBUtil.get();
		String sql = "delete from user where user_id=?";
		boolean flag = true;
		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, id);
			flag = ptmt.execute();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public boolean update(User t) {
		conn = DBUtil.get();
		String sql = "update user"
				+ " set name=?,age=?,sex=?,username=?,password=?,role_id=?"
				+ " where user_id=?";

		boolean flag = true;
		try {

			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, t.getName());
			ptmt.setInt(2, t.getAge());
			ptmt.setInt(3, t.getSex());
			ptmt.setString(4, t.getUsername());
			ptmt.setString(5, t.getPassword());
			ptmt.setInt(6, t.getRoleId());
			ptmt.setInt(7, t.getUserId());

			flag = ptmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public User findById(String id) {
		User user = null;
		conn = DBUtil.get();
		List<User> list = new ArrayList<User>();
		String sql = " select u.* from user as u where u.user_id= ?";
		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, id);
			rs = ptmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUserId(rs.getInt("u.user_id"));
				user.setName(rs.getString("u.name"));
				user.setAge(rs.getInt("u.age"));
				user.setSex(rs.getInt("u.sex"));
				user.setUsername(rs.getString("u.username"));
				user.setPassword(rs.getString("u.password"));
				user.setRoleId(rs.getInt("u.role_id"));
				user.setCreateTime(rs.getString("u.create_time"));
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// 判断是否查询到数据，查询到数据返回第一条数据，否则返回空的user
		if (list.size() > 0) {
			return list.get(0);
		}

		return user;
	}

	@Override
	public List<User> findAll() {
		
		User user = null;
		Role role = null;
		
		conn = DBUtil.get();
		List<User> list = new ArrayList<User>();
		// 左连接查询，连用户的角色一并查出来
		String sql = " select u.*,r.*"
				+ " from user as u"
				+ " left join role as r"
				+ " on u.role_id = r.role_id";
		try {
			ptmt = conn.prepareStatement(sql);
			rs = ptmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUserId(rs.getInt("u.user_id"));
				user.setName(rs.getString("u.name"));
				user.setAge(rs.getInt("u.age"));
				user.setSex(rs.getInt("u.sex"));
				user.setUsername(rs.getString("u.username"));
				user.setPassword(rs.getString("u.password"));
				user.setCreateTime(rs.getString("u.create_time"));
				user.setRoleId(rs.getInt("u.role_id"));
				role = new Role();
				role.setRoleId(rs.getInt("r.role_id"));
				role.setRoleName(rs.getString("r.role_name"));
				user.setRole(role);
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public User findByUsernameAndPassword(String username, String password) {
		
		User user = null;
		Role role = null;
		
		conn = DBUtil.get();
		List<User> list = new ArrayList<User>();
		String sql = " select u.*,r.* from user"
				+ " as u left join role as r"
				+ " on u.role_id = r.role_id"
				+ " where u.username = ?"
				+ " and u.password = ?";
		try {
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, username);
			ptmt.setString(2, password);
			
			rs = ptmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUserId(rs.getInt("u.user_id"));
				user.setName(rs.getString("u.name"));
				user.setAge(rs.getInt("u.age"));
				user.setSex(rs.getInt("u.sex"));
				user.setUsername(rs.getString("u.username"));
				user.setPassword(rs.getString("u.password"));
				user.setCreateTime(rs.getString("u.create_time"));
				user.setRoleId(rs.getInt("u.role_id"));
				role = new Role();
				role.setRoleId(rs.getInt("r.role_id"));
				role.setRoleName(rs.getString("r.role_name"));
				user.setRole(role);
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// 判断是否查询到数据，查询到数据返回第一条数据，否则返回空的user
		if (list.size() > 0) {
			return list.get(0);
		}

		return user;
	}

	public List<User> findByUsername(String username) {
		
		User user = null;
		Role role = null;
		
		conn = DBUtil.get();
		List<User> list = new ArrayList<User>();
		// 使用模糊查询语句
		String sql = " select u.*,r.* from user as u"
				+ " left join role as r"
				+ " on u.role_id = r.role_id"
				+ " where u.username like"
				+ " +'%" + username + "%' ";
		try {
			ptmt = conn.prepareStatement(sql);
			rs = ptmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUserId(rs.getInt("u.user_id"));
				user.setName(rs.getString("u.name"));
				user.setAge(rs.getInt("u.age"));
				user.setSex(rs.getInt("u.sex"));
				user.setUsername(rs.getString("u.username"));
				user.setPassword(rs.getString("u.password"));
				user.setCreateTime(rs.getString("u.create_time"));
				role = new Role();
				role.setRoleId(rs.getInt("r.role_id"));
				role.setRoleName(rs.getString("r.role_name"));
				user.setRole(role);
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ptmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

}
